#!/usr/bin/perl

use strict;
use warnings;

use Getopt::Long;
use File::Find;

my $database = undef;
my $user = undef;
my $password = undef;
my @dirs = ();
my $protocol = "socket";

GetOptions
(
 'database=s' => \$database,
 'user=s' => \$user,
 'password=s' => \$password,
 'dir=s',\@dirs,
 "protocol=s",\$protocol
);

if(!($database and $user and $password and @dirs and $protocol))
{
	print <<EOS;
usage:$0 options(all mandatoy - except those with defaults)
		  --database db-name @{[$database ? "(You said '$database')" : "NOT SUPPLIED"]}
		  --user username @{[$user ? "(You said '$user')" : "NOT SUPPLIED"]}
		  --password password @{[$password ? "(You said '$password')" : "NOT SUPPLIED"]}
		  --dir directory with sql scripts. defaults to ./src/schema @{[@dirs ?  "(You said '@dirs')" : "NOT SUPPLIED"]}
		  --protocol the protocol to use to talk to the server. defaults to
			  socket. can be tcp. see mysql client options documentation.
			  @{[$protocol ?  "(You said '$protocol')" : "NOT SUPPLIED"]}
You can use any unambiguous prefix of the options as well
EOS
	die "Some options not supplied";
}

my @sqlFiles = ();

my %tablesByFile = ();
my %creatorByTable = ();
my %prereqTablesByFile = ();

for my $dir(@dirs)
{
	File::Find::find
	(
		{
			wanted=>
				sub
				{
					my $sqlFile = $_;
	
	
					if(!($sqlFile =~ m/\.sql$/))
					{
						return;
					}
	
					push @sqlFiles,$sqlFile;
	
					open SQL,"<$sqlFile"
						or die "Failed to read from $sqlFile";
	
					my @lines = <SQL>;
	
					close SQL;
	
					$prereqTablesByFile{$sqlFile} = [];
	
					my %prereqTables = ();
	
					foreach my $line(@lines)
					{
						if($line =~ m/create\s+table\s+(\w+)/io)
						{
							$creatorByTable{$1} = $sqlFile;
							if(!$tablesByFile{$sqlFile})
							{
								$tablesByFile{$sqlFile} = [];
							}
							
							push @{$tablesByFile{$sqlFile}},$1;
						}
						elsif
						(
							($line =~ m/insert\s+into\s+(\w+)/oi)
							||
							($line =~ m/delete\s+from\s+(\w+)/oi)
							||
							($line =~ m/update\s+(\w+)/oi)
							||
							($line =~ m/references\s+(\w+)\w*\([\w,]+\)/oi)
						)
						{
							my $prereqTable = $1;
							$prereqTables{$prereqTable} = 1;
						}
					}
	
					push @{$prereqTablesByFile{$sqlFile}},keys %prereqTables;
				},
			no_chdir => 1
		},
		$dir
	);
}

my %visitedSqlFiles = ();

VisitSqlFiles(\@sqlFiles,\%visitedSqlFiles);

@sqlFiles = sort { $visitedSqlFiles{$a} <=> $visitedSqlFiles{$b} } @sqlFiles;

open MYSQL,"|mysql --protocol $protocol -u $user -p$password $database"
	or die "Faield to talk to mysql client:$@";

foreach my $sqlFile(reverse @sqlFiles)
{
	print "Dropping tables defined in $sqlFile ...\n";
	foreach my $table(@{$tablesByFile{$sqlFile}})
	{
		print <<EOS;
DROP $table
EOS
		print MYSQL <<EOS;
DROP TABLE IF EXISTS $table;
EOS
	}
}

foreach my $sqlFile(@sqlFiles)
{
	print "Loading $sqlFile ...\n";
	open SQL,"<$sqlFile"
		or die "Failed to read from $sqlFile";

	my @lines = <SQL>;

	close SQL;

	print MYSQL join("",@lines);
	print MYSQL "\n";
	print join("",@lines);
	print "\n";
}

print MYSQL "\\q";
close MYSQL;

sub VisitSqlFiles
{
	my $sqlFiles = shift;
	my $visitedSqlFiles = shift;

	foreach my $sqlFile(@$sqlFiles)
	{
		if($$visitedSqlFiles{$sqlFile})
		{
			next;
		}

		my @prereqFiles = ();
		foreach my $prereqTable(@{$prereqTablesByFile{$sqlFile}})
		{
			if($creatorByTable{$prereqTable})
			{
				if($sqlFile ne $creatorByTable{$prereqTable})
				{
					push @prereqFiles,$creatorByTable{$prereqTable};
				}
			}
			else
			{
				die "Dont know how to create table $prereqTable required by $sqlFile\n";
			}
		}

		VisitSqlFiles(\@prereqFiles,$visitedSqlFiles);

		if(!($$visitedSqlFiles{$sqlFile}))
		{
			$$visitedSqlFiles{$sqlFile} = (scalar(keys(%$visitedSqlFiles)) + 1);
		}
	}
}

